package com.dao.imp;

import com.bean.Express;
import com.dao.BaseExpressDao;
import com.exception.CodeException;
import com.exception.NumberException;
import com.util.DruidUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: 柏竹
 * @Description: SQL处理 快递
 * @Date_Created_in: 2021-05-20 23:10
 * @Modified_By: Sans
 * @Project: 快递e栈
 */

public class ExpressDaoMysql extends DruidUtil implements BaseExpressDao {
    
    // 查 统计 快递量
    public static final String SQL_CONSOLE = "SELECT COUNT(ID) date1_Size , " +
            "COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW()) OR NULL) data1_Day , " +
            "COUNT(STATUS=0 OR Null) data2_Size , " +
            "COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW()) AND status=0 OR NULL) data2_Day " +
            "FROM EXPRESS";
    // 查 总数 快递信息
    public static final String SQL_FIND_ALL = "SELECT * FROM EXPRESS";
    // 查 分页 快递信息
    public static final String SQL_FIND_LIMIT = "SELECT * FROM EXPRESS LIMIT ? , ? ";
    // 查 指定 快递 id
    public static final String SQL_FIND_BY_ID = "SELECT * FROM EXPRESS WHERE ID = ?";
    // 查 指定 快递 快递单号
    public static final String SQL_FIND_BY_NUMBER = "SELECT * FROM EXPRESS WHERE NUMBER = ?";
    // 查 指定 快递 收件人名称
    public static final String SQL_FIND_BY_USERNAME = "SELECT * FROM EXPRESS WHERE USERNAME = ?";
    // 查 指定 快递 收件人电话
    public static final String SQL_FIND_BY_USERPHONE = "SELECT * FROM EXPRESS WHERE USERPHONE = ?";
    // 查 指定 快递 收件人电话
    public static final String SQL_FIND_BY_USERPHONE_AND_STATUS = "SELECT * FROM EXPRESS WHERE USERPHONE = ? AND STATUS = ?";
    // 查 指定 快递 取件码
    public static final String SQL_FIND_BY_CODE = "SELECT * FROM EXPRESS WHERE CODE = ?";
    // 查 指定 快递 录入人的手机号
    public static final String SQL_FIND_BY_SYSPHONE = "SELECT * FROM EXPRESS WHERE SYSPHONE = ?";
    // 查 指定 快递 入库时间
    public static final String SQL_FIND_BY_INTIME = "SELECT * FROM EXPRESS WHERE INTIME = ?";
    // 查 指定 快递 发货地
    public static final String SQL_FIND_BY_DEPARTURE = "SELECT * FROM EXPRESS WHERE DEPARTURE = ?";
    // 查 指定 快递 目的地
    public static final String SQL_FIND_BY_DESTINATION = "SELECT * FROM EXPRESS WHERE DESTINATION = ?";
    // 查 指定 快递 发货IP
    public static final String SQL_FIND_BY_INUSERIP = "SELECT * FROM EXPRESS WHERE INUSERIP = ?";
    // 查 指定 地区 所有快递的数量
    public static final String SQL_GROUP_BY_DEPARTURE = "SELECT DEPARTURE,COUNT(DEPARTURE) COUNT FROM EXPRESS GROUP BY DEPARTURE"  ;
    
    
    // 录入 快递
    public static final String SQL_INSERT =
            "INSERT INTO EXPRESS (NUMBER,USERNAME,USERPHONE,COMPANY,CODE,INTIME,STATUS,SYSPHONE,DEPARTURE,DESTINATION,INUSERIP) VALUES (?,?,?,?,?,NOW(),0,?,?,?,?)";
    // 修改 快递
    public static final String SQL_UPDATE = "UPDATE EXPRESS SET NUMBER=?,USERNAME=?,COMPANY=?,STATUS=? WHERE ID=? ";
    // 修改 快递 取件
    public static final String SQL_UPDATE_STATUS = "UPDATE EXPRESS SET STATUS = 1 , CODE = NULL , OUTTIME = NOW() WHERE CODE = ? ";
    // 删除 快递
    public static final String SQL_DELETE = "DELETE FROM EXPRESS WHERE ID=?";
    
    
    /**
     * 查 库 全部快递(总数,新增)，代取件快递(总数,新增)
     *
     * @return [{size:总数,day:新增},{size:总数,day:新增}]
     */
    @Override
    public List<Map<String, Integer>> console() {
        
        ArrayList<Map<String,Integer>> data = new ArrayList<>();
        ResultSet result = query(SQL_CONSOLE , null);
        try {
            while (result.next()){
                int data1_Size = result.getInt("date1_Size");
                int data1_Day = result.getInt("data1_Day");
                int data2_Size = result.getInt("data2_Size");
                int data2_Day = result.getInt("data2_Day");
                
                Map data1 = new HashMap();
                data1.put("data1_Size",data1_Size);
                data1.put("data1_Day",data1_Day);
                Map data2 = new HashMap();
                data2.put("data2_Size",data2_Size);
                data2.put("data2_Day",data2_Day);
                
                data.add(data1);
                data.add(data2);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                closeDaoAll();
                if (result != null) {
                    result.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return data;
    }
    
    /**
     * 查 所有 快递信息
     *
     * @param limit      是否分页
     * @param offset     起始索引
     * @param pageNumber 每页查询数
     * @return 快递结果集
     */
    @Override
    public List<Express> findExpressAll(boolean limit , int offset , int pageNumber) {
        List<Express> expressList = new ArrayList<>();
        if (limit){
            List list = new ArrayList();
            list.add(offset);
            list.add(pageNumber);
            query(SQL_FIND_LIMIT,list);
        }else{
            query(SQL_FIND_ALL,null);
        }
        try {
            expressList = refexClass(Express.class);
        } catch (Exception e) {
            e.printStackTrace();
        }
        closeDaoAll();
        return expressList;
    }
    
    /**
     * 查 指定 快递信息
     *
     * @param type 查询类型
     * @param data 查指定信息
     * @return 快递信息
     */
    @Override
    public List<Express> findExpress(String type , String data) {
        String sql;
        List list = new ArrayList();
        switch (type){
            case "id":
                sql = SQL_FIND_BY_ID;
                break;
            case "number":
                sql = SQL_FIND_BY_NUMBER;
                break;
            case "username":
                sql = SQL_FIND_BY_USERNAME;
                break;
            case "userphone":
                sql = SQL_FIND_BY_USERPHONE;
                break;
            case "code":
                sql = SQL_FIND_BY_CODE;
                break;
            case "sysPhone":
                sql = SQL_FIND_BY_SYSPHONE;
                break;
            case "departure":
                sql = SQL_FIND_BY_DEPARTURE;
                break;
            case "destination":
                sql = SQL_FIND_BY_DESTINATION;
                break;
            case "inuserip":
                sql = SQL_FIND_BY_INUSERIP;
                break;
            default:
                return null;
        }
        list.add(data);
        query(sql,list);
        List<Express> expressList = null;
        try {
            expressList = refexClass(Express.class);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            closeDaoAll();
        }
        return expressList;
    }
    
    /**
     * 查 指定 手机号、状态 快递信息
     *
     * @param userPhone 手机号
     * @param status    状态值
     * @return 快递信息
     */
    @Override
    public List<Express> findByUserPhoneAndStatus(String userPhone , int status) {
        List<Express> expressList = null;
        List list = new ArrayList();
        list.add(userPhone);
        list.add(status);
        query(SQL_FIND_BY_USERPHONE_AND_STATUS,list);
        try {
            expressList = refexClass(Express.class);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeDaoAll();
        }
        return expressList;
    }
    
    /**
     * 地区数量
     * @return
     */
    @Override
    public List departureAllSize(){
        ResultSet query = query(SQL_GROUP_BY_DEPARTURE , null);
        List list = new ArrayList();
        try {
            while(query.next()){
                Map<String,Integer> map = new HashMap<>();
                String departure = query.getString("DEPARTURE");
                Integer count = query.getInt("COUNT");
                map.put(departure,count);
                list.add(map);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                if (query != null) {
                    query.close();
                }
                closeDaoAll();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return list;
    }
    
    
    /**
     * 录入 快递
     *
     * @param e 快递对象
     * @return 是否成功
     */
    @Override
    public boolean insert(Express e) throws CodeException, NumberException {
        List list = new ArrayList();
        list.add(e.getNumber());
        list.add(e.getUsername());
        list.add(e.getUserphone());
        list.add(e.getCompany());
        list.add(e.getCode());
        list.add(e.getSysPhone());
        list.add(e.getDeparture());
        list.add(e.getDestination());
        list.add(e.getInuserip());
        boolean find = false;
        try {
            find = update(SQL_INSERT,list)>0?true:false;
        } catch (SQLException throwables) {
            String info = throwables.getMessage();
            if (info.endsWith("for key 'express.code'")){
                CodeException codeException = new CodeException(info);
                throw codeException;
            }else{
                if(info.endsWith("for key 'express.number'")){
                    NumberException numberException = new NumberException(info);
                    throw numberException;
                }else{
                    throwables.printStackTrace();
                }
            }
        }
        closeDaoAll();
        return find;
    }
    
    /**
     * 修改 快递
     *
     * @param id           快递id
     * @param newException 快递对象
     * @return 是否成功
     */
    @Override
    public boolean update(int id , Express newException) {
        // UPDATE EXPRESS SET NUMBER=?,USERNAME=?,COMPANY=?,STATUS=? WHERE ID=?
        List list = new ArrayList();
        list.add(newException.getNumber());
        list.add(newException.getUsername());
        list.add(newException.getCompany());
        list.add(newException.getStatus());
        list.add(id);
        Boolean find = false;
        try {
            find = update(SQL_UPDATE , list) > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeDaoAll();
        }
        return find;
    }
    
    /**
     * 修改 快递 (取件)
     *
     * @param code 取件号
     * @return 是否成功
     */
    @Override
    public boolean updateStatus(String code) {
        List list = new ArrayList();
        list.add(code);
        Boolean find = false;
        try {
            find = update(SQL_UPDATE_STATUS , list) > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeDaoAll();
        }
        return find;
    }
    
    /**
     * 删除 快递
     *
     * @param id 快递id
     * @return 是否成功
     */
    @Override
    public boolean delete(int id) {
        //DELETE FROM EXPRESS WHERE ID=?
        List list = new ArrayList();
        list.add(id);
        Boolean find = false;
        try {
            find = update(SQL_DELETE , list) > 0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeDaoAll();
        }
        return find;
    }
}
